-- SELECT * FROM FN_GET_ADAPTOR_RUNINFO_STATUS() WHERE ADAPTOR_NAME = 'CHARTERED_FAB'
alter  FUNCTION FN_GET_ADAPTOR_RUNINFO_STATUS
(
	
)
RETURNS @RESULTS TABLE( adaptor_name VARCHAR(90), updated_time DATETIME, Adaptor_File_Name VARCHAR(5000), Upload_Date VARCHAR(5000) )
--RETURNS @RESULTS TABLE( DEBUG_STRING VARCHAR(500) )
AS
BEGIN
							
	DECLARE @RUNINFO_CURSOR 			CURSOR

	DECLARE @MAIN_ADAPTOR_NAME 		VARCHAR(50)

	DECLARE @ADAPTOR_NAME		VARCHAR(90)
	DECLARE @UPDATED_TIME		DATETIME
	DECLARE @FILE_NAME		   VARCHAR(200)
	DECLARE @FILE_UPLOAD_TIME	VARCHAR(50)
	
	
	SET @RUNINFO_CURSOR = CURSOR FOR SELECT ADAPTOR_NAME, UPDATED_TIME, FILE_NAME, FILE_UPLOAD_TIME FROM ADAPTOR_RUNINFO_TABLE ART, ADAPTOR_FILEINFO_TABLE AFT 
								WHERE AFT.ADAPTOR_ID = ART.ADAPTOR_FILE_INFO_ID AND UPDATED_TIME = (SELECT MAX(UPDATED_TIME) FROM ADAPTOR_RUNINFO_TABLE ARI 
														WHERE ART.ADAPTOR_NAME = ARI.ADAPTOR_NAME AND ADAPTOR_TYPE = 'PARSER' AND ADAPTOR_STATUS ='SUCCESSFUL') ORDER BY UPDATED_TIME
	OPEN @RUNINFO_CURSOR

	FETCH NEXT FROM @RUNINFO_CURSOR INTO @ADAPTOR_NAME,@UPDATED_TIME,@FILE_NAME,@FILE_UPLOAD_TIME
	WHILE @@FETCH_STATUS = 0
	BEGIN
				IF @MAIN_ADAPTOR_NAME IS NULL OR @MAIN_ADAPTOR_NAME = ''
				BEGIN
						SET @MAIN_ADAPTOR_NAME = @ADAPTOR_NAME
						INSERT INTO @RESULTS(ADAPTOR_NAME,UPDATED_TIME,Adaptor_File_Name,Upload_Date) 
								VALUES(@ADAPTOR_NAME,@UPDATED_TIME,@FILE_NAME,@FILE_UPLOAD_TIME)
				END
				ELSE IF @MAIN_ADAPTOR_NAME = @ADAPTOR_NAME
				BEGIN
						UPDATE @RESULTS SET Adaptor_File_Name = Adaptor_File_Name + '<br>' + @FILE_NAME, Upload_Date = Upload_Date + '<br>' + @FILE_UPLOAD_TIME  WHERE ADAPTOR_NAME = @ADAPTOR_NAME
				END
				ELSE
				BEGIN
						SET @MAIN_ADAPTOR_NAME = @ADAPTOR_NAME
						INSERT INTO @RESULTS(ADAPTOR_NAME,UPDATED_TIME,Adaptor_File_Name,Upload_Date) 
								VALUES(@ADAPTOR_NAME,@UPDATED_TIME,@FILE_NAME,@FILE_UPLOAD_TIME)
				END

		SET @ADAPTOR_NAME	= NULL
		SET @UPDATED_TIME		= NULL
		SET @FILE_NAME			= NULL
		SET @FILE_UPLOAD_TIME		= NULL		
					
		FETCH NEXT FROM @RUNINFO_CURSOR INTO @ADAPTOR_NAME,@UPDATED_TIME,@FILE_NAME,@FILE_UPLOAD_TIME
	END
	CLOSE @RUNINFO_CURSOR
	DEALLOCATE @RUNINFO_CURSOR
	
	RETURN
END
